import { NextRequest, NextResponse } from 'next/server';
import { getDeviceParameterDb } from '@/lib/device-parameter-database';
import { verifyAuth, verifyCreatePermission, verifyDeletePermission } from '@/lib/auth-middleware';

export interface DeviceInstance {
  id: number;
  device_type_code: string;
  instance_number: string;
  object_number: string;
  object_description: string;
  pbs_level_id?: number;
  pbs_instance_id?: string;
  created_at: string;
  updated_at: string;
  tempInstanceId?: number; // 临时实例ID，用于新建设备
  parameters?: {
    naming: Record<string, any>;
    position: Record<string, any>;
    base_params: Record<string, any>;
  };
  // 支持动态嵌入字段
  [key: string]: any;
}

// GET /api/device-instances?deviceTypeCode=L4-06-01-01-01-000
export async function GET(request: NextRequest) {
  try {
    // 验证用户认证状态
    const authResult = await verifyAuth();
    if (!authResult.success) {
      return NextResponse.json({
        success: false,
        error: authResult.error
      }, { status: authResult.status || 401 });
    }
    const { searchParams } = new URL(request.url);
    const deviceTypeCode = searchParams.get('deviceTypeCode');
    
    if (!deviceTypeCode) {
      return NextResponse.json({
        success: false,
        error: '缺少设备类型代码参数'
      }, { status: 400 });
    }
    
    const db = getDeviceParameterDb();
    const deviceParameterDb = db as any;
    
    // 找到正确的设备实例表
    // 使用模板导入格式：device_instances_${specialtyPrefix}_${english_name}
    let actualTableName = null;
    
    try {
      // 首先获取设备信息以找到正确的实例表
      // 前端可能传递设备ID或分类码，需要两种查询都支持
      let deviceInfo = null;
      
      // 先尝试按设备ID查询
      const deviceByIdStmt = deviceParameterDb.db.prepare(`
        SELECT d.*, dtm.english_name, dtm.specialty 
        FROM devices d
        LEFT JOIN device_type_mappings dtm ON d.classification_code = dtm.classification_code
        WHERE d.device_id = ?
      `);
      deviceInfo = deviceByIdStmt.get(deviceTypeCode);
      
      // 如果按设备ID没找到，再尝试按分类码查询
      if (!deviceInfo) {
        const deviceByClassificationStmt = deviceParameterDb.db.prepare(`
          SELECT d.*, dtm.english_name, dtm.specialty 
          FROM devices d
          LEFT JOIN device_type_mappings dtm ON d.classification_code = dtm.classification_code
          WHERE d.classification_code = ?
        `);
        deviceInfo = deviceByClassificationStmt.get(deviceTypeCode);
      }
      
      if (deviceInfo && deviceInfo.english_name) {
        // 尝试使用模板导入格式的表名
        const specialtyPrefixes: Record<string, string> = {
          "配管专业": "piping",
          "电气专业": "electrical", 
          "暖通专业": "hvac",
          "通信专业": "communication",
          "仪表专业": "instrumentation"
        };
        
        const specialtyPrefix = specialtyPrefixes[deviceInfo.specialty];
        if (specialtyPrefix) {
          const templateImportTableName = `device_instances_${specialtyPrefix}_${deviceInfo.english_name}`;
          
          // 检查模板导入格式的表是否存在
          const checkTemplateTableStmt = deviceParameterDb.db.prepare(`
            SELECT name FROM sqlite_master 
            WHERE type='table' AND name=?
          `);
          const templateTableExists = checkTemplateTableStmt.get(templateImportTableName);
          
          if (templateTableExists) {
            actualTableName = templateImportTableName;
            console.log(`使用模板导入表格式: ${actualTableName}`);
          }
        }
      }
      
      // 如果没有找到模板导入格式的表，返回错误
      if (!actualTableName) {
        return NextResponse.json({
          success: false,
          error: `未找到设备 ${deviceInfo.english_name} 的实例表，请检查设备配置`
        }, { status: 400 });
      }
      
      // 验证表存在
      const checkTableStmt = deviceParameterDb.db.prepare(`
        SELECT name FROM sqlite_master 
        WHERE type='table' AND name=?
      `);
      const tableExists = checkTableStmt.get(actualTableName);
      
      if (!tableExists) {
        return NextResponse.json({
          success: false,
          error: `实例表 ${actualTableName} 不存在，请联系管理员`
        }, { status: 500 });
      }
      
      console.log(`使用实例表: ${actualTableName}`);
      
      // 查询设备实例
      // 使用实际的设备ID查询实例，而不是前端传来的参数
      const actualDeviceId = deviceInfo.device_id;
      const stmt = deviceParameterDb.db.prepare(`
        SELECT * FROM [${actualTableName}]
        WHERE device_type_code = ?
        ORDER BY created_at
      `);
      const rawInstances = stmt.all(actualDeviceId);
      
      // 标准化实例数据，统一处理两种参数存储模式
      const instances = rawInstances.map(instance => {
        const normalized: DeviceInstance = {
          id: instance.id,
          device_type_code: instance.device_type_code,
          instance_number: instance.instance_number,
          object_number: instance.object_number,
          object_description: instance.object_description,
          pbs_level_id: instance.pbs_level_id,
          pbs_instance_id: instance.pbs_instance_id,
          created_at: instance.created_at,
          updated_at: instance.updated_at,
          parameters: {
            naming: {},
            position: {},
            base_params: {}
          }
        };

        // 数据库使用增强结构，无record_id字段
        // 移除record_id相关逻辑，直接处理嵌入字段

        // 处理直接字段嵌入模式
        Object.keys(instance).forEach(key => {
          if (key.startsWith('naming_') && instance[key] !== null && instance[key] !== '') {
            normalized.parameters.naming[key.replace('naming_', '')] = instance[key];
          } else if (key.startsWith('position_') && instance[key] !== null && instance[key] !== '') {
            normalized.parameters.position[key.replace('position_', '')] = instance[key];
          } else if (key.startsWith('base_params_') && instance[key] !== null && instance[key] !== '') {
            normalized.parameters.base_params[key.replace('base_params_', '')] = instance[key];
          }
        });

        return normalized;
      });
      
      return NextResponse.json({
        success: true,
        data: instances
      });
      
    } catch (error) {
      console.error('数据库操作失败:', error);
      return NextResponse.json({
        success: false,
        error: '数据库操作失败'
      }, { status: 500 });
    }
    
  } catch (error) {
    console.error('获取设备实例失败:', error);
    return NextResponse.json({
      success: false,
      error: error instanceof Error ? error.message : '获取设备实例失败'
    }, { status: 500 });
  }
}

// POST /api/device-instances
export async function POST(request: NextRequest) {
  try {
    // 验证用户创建权限
    const authResult = await verifyCreatePermission();
    if (!authResult.success) {
      return NextResponse.json({
        success: false,
        error: authResult.error
      }, { status: authResult.status || 401 });
    }

    if (!authResult.canCreate) {
      return NextResponse.json({
        success: false,
        error: '权限不足：无法创建设备实例'
      }, { status: 403 });
    }
    const instanceData = await request.json();
    
    // 验证必要字段
    if (!instanceData.device_type_code || !instanceData.object_number) {
      return NextResponse.json({
        success: false,
        error: '缺少必要字段: device_type_code, object_number'
      }, { status: 400 });
    }

    console.log('创建设备实例，接收到的数据:', instanceData);
    
    const db = getDeviceParameterDb();
    const deviceParameterDb = db as any;
    
    // 先获取设备信息以找到正确的实例表名
    let deviceInfo = null;
    try {
      const deviceStmt = deviceParameterDb.db.prepare(`
        SELECT d.*, dtm.english_name, dtm.specialty 
        FROM devices d
        LEFT JOIN device_type_mappings dtm ON d.classification_code = dtm.classification_code
        WHERE d.device_id = ?
      `);
      deviceInfo = deviceStmt.get(instanceData.device_type_code);
    } catch (error) {
      console.error('查询设备信息失败:', error);
    }

    if (!deviceInfo) {
      return NextResponse.json({
        success: false,
        error: `未找到设备 ${instanceData.device_type_code} 的信息`
      }, { status: 400 });
    }

    // 使用正确的表名逻辑：只使用模板导入格式
    let actualTableName = null;
    
    if (deviceInfo && deviceInfo.english_name) {
      // 尝试使用模板导入格式的表名
      const specialtyPrefixes: Record<string, string> = {
        "配管专业": "piping",
        "电气专业": "electrical", 
        "暖通专业": "hvac",
        "通信专业": "communication",
        "仪表专业": "instrumentation"
      };
      
      const specialtyPrefix = specialtyPrefixes[deviceInfo.specialty];
      if (specialtyPrefix) {
        const templateImportTableName = `device_instances_${specialtyPrefix}_${deviceInfo.english_name}`;
        
        // 检查模板导入格式的表是否存在
        const checkTemplateTableStmt = deviceParameterDb.db.prepare(`
          SELECT name FROM sqlite_master 
          WHERE type='table' AND name=?
        `);
        const templateTableExists = checkTemplateTableStmt.get(templateImportTableName);
        
        if (templateTableExists) {
          actualTableName = templateImportTableName;
          console.log(`使用模板导入表格式: ${actualTableName}`);
        }
      }
    }
    
    // 如果没有找到模板导入格式的表，报错（因为所有表都应该已经存在）
    if (!actualTableName) {
      return NextResponse.json({
        success: false,
        error: `未找到设备 ${deviceInfo.english_name} 的实例表，请检查设备配置`
      }, { status: 400 });
    }
    
    try {
      // 验证表存在（所有表都应该已经存在）
      const checkTableStmt = deviceParameterDb.db.prepare(`
        SELECT name FROM sqlite_master 
        WHERE type='table' AND name=?
      `);
      const tableExists = checkTableStmt.get(actualTableName);
      
      if (!tableExists) {
        return NextResponse.json({
          success: false,
          error: `实例表 ${actualTableName} 不存在，请联系管理员`
        }, { status: 500 });
      }
      
      console.log(`使用实例表: ${actualTableName}`);

      // 获取设备类型映射信息
      const deviceMapping = db.getDeviceTypeMapping(deviceInfo.classification_code);
      if (!deviceMapping) {
        return NextResponse.json({
          success: false,
          error: `未找到分类代码 ${deviceInfo.classification_code} 的参数表映射`
        }, { status: 400 });
      }

      console.log('设备映射信息:', {
        device_id: instanceData.device_type_code,
        classification_code: deviceInfo.classification_code,
        deviceMapping
      });

      // 添加重试机制处理并发冲突
      const MAX_RETRIES = 3;
      let retryCount = 0;
      let finalResult: any;
      
      while (retryCount < MAX_RETRIES) {
        try {
          // 使用事务确保操作原子性
          finalResult = deviceParameterDb.db.transaction(() => {
            // 生成实例编号（使用MAX+1算法避免并发冲突）
            const maxStmt = deviceParameterDb.db.prepare(`
              SELECT COALESCE(MAX(CAST(instance_number AS INTEGER)), 0) as max_number 
              FROM [${actualTableName}] 
              WHERE device_type_code = ?
            `);
            const result = maxStmt.get(instanceData.device_type_code) as { max_number: number };
            const instanceNumber = String(result.max_number + 1).padStart(3, '0');
            
            console.log(`生成实例编号: ${instanceNumber} (基于最大编号: ${result.max_number})`);
            
            // 准备嵌入字段数据（使用增强结构）
            let embeddedFields: Record<string, any> = {};

            if (instanceData.parameterData) {
              console.log('🔍 [DEBUG] 收到参数数据:', JSON.stringify(instanceData.parameterData, null, 2));
              console.log('🔍 [DEBUG] 设备映射信息:', JSON.stringify({
                naming_table: deviceMapping.naming_table,
                position_table: deviceMapping.position_table,
                base_params_table: deviceMapping.base_params_table
              }, null, 2));
        
              // 转换字段ID到字段名的函数
              const convertFieldIdDataToEmbeddedFields = (fieldIdData: Record<string, string>, tableName: string, prefix: string) => {
                console.log(`=== convertFieldIdDataToEmbeddedFields 调用 ===`);
                console.log(`tableName: ${tableName}, prefix: ${prefix}`);
                console.log(`fieldIdData:`, fieldIdData);
                
                const fieldMetadata = db.getParameterTableFields(tableName);
                console.log(`字段元数据获取结果 (${tableName}):`, fieldMetadata.length, '个字段');
                
                Object.entries(fieldIdData).forEach(([fieldId, value]) => {
                  console.log(`处理字段ID: ${fieldId}, 值: ${value}`);
                  const field = fieldMetadata.find(f => f.id === parseInt(fieldId));
                  if (field) {
                    console.log(`找到字段元数据:`, {
                      id: field.id,
                      field_name: field.field_name,
                      table_name: field.table_name
                    });
                    
                    if (value && String(value).trim()) {
                      const embeddedFieldName = `${prefix}_${field.field_name}`;
                      embeddedFields[embeddedFieldName] = String(value).trim();
                      console.log(`✓ 字段转换成功: ID ${fieldId} -> ${embeddedFieldName} = ${value}`);
                    } else {
                      console.log(`✗ 字段值为空，跳过: ID ${fieldId}`);
                    }
                  } else {
                    console.log(`✗ 未找到字段元数据: ID ${fieldId}`);
                  }
                });
              };
              
              // 处理命名信息参数
              if (instanceData.parameterData.naming && Object.keys(instanceData.parameterData.naming).length > 0) {
                convertFieldIdDataToEmbeddedFields(instanceData.parameterData.naming, deviceMapping.naming_table, 'naming');
              }

              // 处理位置信息参数
              if (instanceData.parameterData.position && Object.keys(instanceData.parameterData.position).length > 0) {
                convertFieldIdDataToEmbeddedFields(instanceData.parameterData.position, deviceMapping.position_table, 'position');
              }

              // 处理基础参数
              if (instanceData.parameterData.base_params && Object.keys(instanceData.parameterData.base_params).length > 0) {
                convertFieldIdDataToEmbeddedFields(instanceData.parameterData.base_params, deviceMapping.base_params_table, 'base_params');
              }
            }
            
            console.log('=== 最终嵌入字段准备完成 ===');
            console.log('嵌入字段数量:', Object.keys(embeddedFields).length);
            console.log('嵌入字段列表:', Object.keys(embeddedFields));
            console.log('嵌入字段详情:', embeddedFields);
            
            // 构建插入语句（使用增强结构的嵌入字段）
            const baseFields = ['device_type_code', 'instance_number', 'object_number', 'object_description', 'pbs_level_id', 'pbs_instance_id'];
            const allFields = [...baseFields, ...Object.keys(embeddedFields)];
            const placeholders = allFields.map(() => '?').join(', ');
            
            console.log('🔍 [DEBUG] === SQL构建阶段 ===');
            console.log('🔍 [DEBUG] SQL字段列表:', allFields);
            console.log('🔍 [DEBUG] 占位符:', placeholders);
            
            const sqlQuery = `INSERT INTO [${actualTableName}] (${allFields.map(f => `[${f}]`).join(', ')}) VALUES (${placeholders})`;
            console.log('🔍 [DEBUG] 完整SQL语句:', sqlQuery);
            
            const insertStmt = deviceParameterDb.db.prepare(sqlQuery);
            console.log('🔍 [DEBUG] SQL语句准备完成');
            
            const baseValues = [
              instanceData.device_type_code,
              instanceNumber,
              instanceData.object_number,
              instanceData.object_description || '',
              instanceData.pbs_level_id || null,
              instanceData.pbs_instance_id || null
            ];
            const embeddedValues = Object.values(embeddedFields);
            const allValues = [...baseValues, ...embeddedValues];
            
            console.log('🔍 [DEBUG] === SQL执行参数 ===');
            console.log('🔍 [DEBUG] 基础值:', baseValues);
            console.log('🔍 [DEBUG] 嵌入字段值:', embeddedValues);
            console.log('🔍 [DEBUG] 所有值:', allValues);
            console.log('🔍 [DEBUG] 值的数量:', allValues.length, '字段数量:', allFields.length);
            
            let insertResult;
            try {
              insertResult = insertStmt.run(...allValues);
              console.log('✅ [SUCCESS] SQL执行成功，插入结果:', {
                lastInsertRowid: insertResult.lastInsertRowid,
                changes: insertResult.changes
              });
            } catch (sqlError) {
              console.error('❌ [ERROR] SQL执行失败:', sqlError);
              throw sqlError;
            }

            console.log('🔍 [DEBUG] 设备实例创建完成:', {
              instanceId: insertResult.lastInsertRowid,
              instanceNumber,
              embeddedFields: Object.keys(embeddedFields)
            });
            
            return {
              id: insertResult.lastInsertRowid,
              device_type_code: instanceData.device_type_code,
              instance_number: instanceNumber,
              object_number: instanceData.object_number,
              object_description: instanceData.object_description || '',
              pbs_level_id: instanceData.pbs_level_id || null,
              pbs_instance_id: instanceData.pbs_instance_id || null,
              ...embeddedFields
            };
          })(); // 立即执行事务
          
          // 事务成功完成，跳出重试循环
          break;
          
        } catch (error: any) {
          retryCount++;
          console.log(`实例创建尝试 ${retryCount} 失败:`, error.message);
          
          if (error.code === 'SQLITE_CONSTRAINT_UNIQUE' && retryCount < MAX_RETRIES) {
            console.log(`实例编号冲突，进行第 ${retryCount + 1} 次重试...`);
            // 短暂等待后重试
            await new Promise(resolve => setTimeout(resolve, 10));
            continue;
          } else {
            // 最大重试次数已达到或其他错误
            throw error;
          }
        }
      }
      
      return NextResponse.json({
        success: true,
        data: finalResult
      });
      
    } catch (error: any) {
      console.error('创建设备实例操作失败:', error);
      
      if (error.code === 'SQLITE_CONSTRAINT_UNIQUE') {
        return NextResponse.json({
          success: false,
          error: '实例编号冲突，请重试'
        }, { status: 409 });
      }
      
      throw error;
    }
    
  } catch (error) {
    console.error('创建设备实例失败:', error);
    return NextResponse.json({
      success: false,
      error: error instanceof Error ? error.message : '创建设备实例失败'
    }, { status: 500 });
  }
}

// DELETE /api/device-instances/[id]
export async function DELETE(request: NextRequest) {
  try {
    // 验证用户删除权限
    const authResult = await verifyDeletePermission();
    if (!authResult.success) {
      return NextResponse.json({
        success: false,
        error: authResult.error
      }, { status: authResult.status || 401 });
    }

    if (!authResult.canDelete) {
      return NextResponse.json({
        success: false,
        error: '权限不足：无法删除设备实例'
      }, { status: 403 });
    }
    const { searchParams } = new URL(request.url);
    const instanceId = searchParams.get('id');
    
    if (!instanceId) {
      return NextResponse.json({
        success: false,
        error: '缺少设备实例ID参数'
      }, { status: 400 });
    }
    
    const db = getDeviceParameterDb();
    const deviceParameterDb = db as any;
    
    // 由于我们使用的是按设备类型分表的结构，我们需要找到正确的表
    // 这里需要先查询实例信息来确定表名
    const tablesStmt = deviceParameterDb.db.prepare(`
      SELECT name FROM sqlite_master 
      WHERE type='table' AND name LIKE 'device_instances_%'
    `);
    const tables = tablesStmt.all();
    
    for (const table of tables) {
      try {
        const checkStmt = deviceParameterDb.db.prepare(`
          SELECT * FROM ${table.name} WHERE id = ?
        `);
        const instance = checkStmt.get(instanceId);
        
        if (instance) {
          // 找到了实例，删除它
          const deleteStmt = deviceParameterDb.db.prepare(`
            DELETE FROM ${table.name} WHERE id = ?
          `);
          deleteStmt.run(instanceId);
          
          return NextResponse.json({
            success: true,
            message: '设备实例已删除'
          });
        }
      } catch (error) {
        // 继续检查下一个表
        continue;
      }
    }
    
    return NextResponse.json({
      success: false,
      error: '未找到指定的设备实例'
    }, { status: 404 });
    
  } catch (error) {
    console.error('删除设备实例失败:', error);
    return NextResponse.json({
      success: false,
      error: error instanceof Error ? error.message : '删除设备实例失败'
    }, { status: 500 });
  }
}